﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Qianli.DirSir.Common;
using Qianli.DirSir.Model;
using System.Data;
using System.Data.SqlClient;
namespace Qianli.DirSir.DAL
{
    public class OrderInfoDAL
    {
        SQLHelper sqlhelper = new SQLHelper();
        /// <summary>
        /// 获取订单信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<OrderInfo> ShowOrderInfo()
        {
            string sql = "select OrderState,OrderID,StoreName,DHLUPSFEDEXEMSName,UserName,OrderCoding,InOrderTime,Opeople,OrderInfo.Address from OrderInfo join StoreInfo on OrderInfo.StoreID=StoreInfo.StoreID join DHLUPSFEDEXEMSInfo on DHLUPSFEDEXEMSInfo.DHLUPSFEDEXEMSID=OrderInfo.DHLUPSFEDEXEMSID join Userinfo on Userinfo.UserID=OrderInfo.UserID ";
            DataTable dt = sqlhelper.ExecDataTable(sql);
            List<OrderInfo> list = new List<OrderInfo>();
            foreach (DataRow item in dt.Rows)
            {
                OrderInfo OrderInfo = new OrderInfo();
                OrderInfo.OrderID = Convert.ToInt32(item["OrderID"]);
                OrderInfo.OrderState = Convert.ToInt32(item["OrderState"]);
                OrderInfo.StoreName = item["StoreName"].ToString();
                OrderInfo.UserName = item["UserName"].ToString();
                OrderInfo.DHLUPSFEDEXEMSName = item["DHLUPSFEDEXEMSName"].ToString();
                OrderInfo.OrderCoding = item["OrderCoding"].ToString();
                OrderInfo.InOrderTime = Convert.ToDateTime(item["InOrderTime"]);
                OrderInfo.Opeople = item["Opeople"].ToString();
                OrderInfo.Address = item["Address"].ToString();
                OrderInfo.InOrderTime = Convert.ToDateTime(item["InOrderTime"]);
                list.Add(OrderInfo);
            }
            return list;
        }
        /// <summary>
        /// 添加商品订单信息
        /// </summary>
        /// <param name="oo"></param>
        /// <returns></returns>
        public int AddOrderInfo(OrderInfo oo, string xml)
        {
            string str = "proc_AddOrderInfo";
            SqlParameter[] paramter = { 
                                      new SqlParameter("@StoreID",oo.StoreID),
                                      new SqlParameter("@DHLUPSFEDEXEMSID",oo.DHLUPSFEDEXEMSID),
                                      new SqlParameter("@UserID",oo.UserID),
                                       new SqlParameter("@OrderCoding",oo.OrderCoding),
                                       new SqlParameter("@Opeople",oo.Opeople),
                                       new SqlParameter("@Ophone",oo.Ophone),
                                       new SqlParameter("@province",oo.province),
                                       new SqlParameter("@Address",oo.Address),
                                       new SqlParameter("@OrderState",oo.OrderState),
                                       new SqlParameter("@StorehouseID",oo.StorehouseID),
                                       new SqlParameter("@xml",xml)
                                      };
            return sqlhelper.ExecNonquery_Proc(str, paramter);
        }
        /// <summary>
        /// 根据ID获取订单子表信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<OrderSonInfo> oo(int id)
        {
            string sql = "select GoodsName,GoodsCoding,GoodsSum,Price from OrderSonInfo join GoodsInfo on GoodsInfo.GoodsID=OrderSonInfo.GoodsID where OrderID=@id";
            SqlParameter[] parameter = { 
                                       new SqlParameter("@id",SqlDbType.Int)
                                       };
            parameter[0].Value = id;
            DataTable dt = sqlhelper.ExecDataTable(sql,parameter);
            List<OrderSonInfo> list = new List<OrderSonInfo>();
            foreach (DataRow item in dt.Rows)
            {
                OrderSonInfo oo = new OrderSonInfo();
                oo.GoodsSum = Convert.ToInt32(item["GoodsSum"]);
                oo.Price = Convert.ToDecimal(item["Price"]);
                oo.GoodsCoding = item["GoodsCoding"].ToString();
                oo.GoodsName = item["GoodsName"].ToString();
                list.Add(oo);
            }
            return list;
        }
        /// <summary>
        /// 根据ID获取订单主表信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public OrderInfo GetOrderInfoByid(int id)  
        {
            string sql = "select OrderID,OrderCoding,StoreName,DHLUPSFEDEXEMSName,StorehouseName,Opeople,Ophone,province,OrderInfo.Address from  OrderInfo join StoreInfo on StoreInfo.StoreID=OrderInfo.StoreID join DHLUPSFEDEXEMSInfo on DHLUPSFEDEXEMSInfo.DHLUPSFEDEXEMSID=OrderInfo.DHLUPSFEDEXEMSID join StorehouseInfo on StorehouseInfo.StorehouseID=OrderInfo.StorehouseID where OrderID=@id";
            SqlParameter[] parameter = { 
                                       new SqlParameter("@id",SqlDbType.Int)
                                       };
            parameter[0].Value = id;
            SqlDataReader reader =  sqlhelper.ExecReader(sql,parameter);
            OrderInfo oo = new OrderInfo();
            while(reader.Read())
            {
                oo.OrderID = Convert.ToInt32(reader["OrderID"]);
                oo.OrderCoding = reader["OrderCoding"].ToString();
                oo.StoreName = reader["StoreName"].ToString();
                oo.DHLUPSFEDEXEMSName = reader["DHLUPSFEDEXEMSName"].ToString();
                oo.StorehouseName = reader["StorehouseName"].ToString();
                oo.Opeople = reader["Opeople"].ToString();
                oo.Ophone = reader["Ophone"].ToString();
                oo.province = reader["province"].ToString();
                oo.Address = reader["Address"].ToString();
            }
            return oo;
        }
        /// <summary>
        /// 商品入库，增加库存数量
        /// </summary>
        /// <param name="orderID"></param>
        /// <returns></returns>
        public int Add_ruku(int orderID)
        {
            SqlParameter[] Parameter = {
                                       new SqlParameter("@orderID",SqlDbType.Int,4)};
            Parameter[0].Value = orderID;
            return sqlhelper.ExecNonquery_Proc("Proc_ruku",Parameter);
        }
        /// <summary>
        /// 根据年，月，日
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="End_Time"></param>
        /// <returns></returns>
        public List<OrderInfo> SelYear(string StartTime,string EndTime)
        {
            string sql = "select SUM(GoodsSum) as SumMoney,year(InOrderTime) as Tdate from (OrderInfo o join OrderSonInfo s on o.OrderID=s.OrderID)  group by year(InOrderTime)  having  year(InOrderTime) between "+StartTime+" and "+EndTime+" order by year(InOrderTime) asc ";
            DataTable table=sqlhelper.ExecDataTable(sql);
            List<OrderInfo> list = new List<OrderInfo>();
            foreach (DataRow item in table.Rows)
            {
                OrderInfo order = new OrderInfo();
                order.SumMoney = item["SumMoney"].ToString();
                order.Tdate = item["Tdate"].ToString();
                list.Add(order);
            }
            return list;
        }
        public List<OrderInfo> SelMonth(string StartTime,string EndTime,string YearTime)
        {
            string sql = "select SUM(GoodsSum) as SumMoney,month(InOrderTime) as Tdate from (OrderInfo o join OrderSonInfo s on o.OrderID=s.OrderID)  group by InOrderTime  having  month(InOrderTime) between "+StartTime+" and "+EndTime+" and year(InOrderTime)="+YearTime+" order by month(InOrderTime) asc ";
            DataTable table = sqlhelper.ExecDataTable(sql);
            List<OrderInfo> list = new List<OrderInfo>();
            foreach (DataRow item in table.Rows)
            {
                OrderInfo order = new OrderInfo();
                order.SumMoney = item["SumMoney"].ToString();
                order.Tdate = item["Tdate"].ToString();
                list.Add(order);
            }
            return list;
        }
        public List<OrderInfo> SelDay(string StartTime,string EndTime,string YearTime,string MonthTime)
        {
            string sql = "select SUM(GoodsSum) as SumMoney,day(InOrderTime) as Tdate from (OrderInfo o join OrderSonInfo s on o.OrderID=s.OrderID)  group by InOrderTime  having  day(InOrderTime) between "+StartTime+" and "+EndTime+" and year(InOrderTime)="+YearTime+" and MONTH(InOrderTime)="+MonthTime+" order by day(InOrderTime) asc ";
            DataTable table = sqlhelper.ExecDataTable(sql);
            List<OrderInfo> list = new List<OrderInfo>();
            foreach (DataRow item in table.Rows)
            {
                OrderInfo order = new OrderInfo();
                order.SumMoney = item["SumMoney"].ToString();
                order.Tdate = item["Tdate"].ToString();
                list.Add(order);
            }
            return list;
        }
        /// <summary>
        /// 根据ID获取订单状态
        /// </summary>
        /// <returns></returns>
        public int GetByOrder(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select OrderState from OrderInfo where OrderID=@id");
            SqlParameter[] paramter = { 
                                    new SqlParameter("@id",SqlDbType.Int)
                                    };
            paramter[0].Value = id;
            return int.Parse(sqlhelper.ExecScalar(builder.ToString(),paramter).ToString());
        }
    }
}
